package util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Random;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


public class SQLGenerate {
	public static void main(String[] args) throws IOException {
		System.out.println(updateRegionYahooName());
	}
	private static int OFFSET = 1;
	
	enum VoucherStatus {
		PURCHASED(2),
		REDEEMED(3),
		REFUND_PENDING(4),
		REFUNDED(5),
		CANCELLED(6);
		
		private int id;
		
		VoucherStatus(int id) {
			this.id = id;
		}
	}
	
	public static String updateRegionYahooName() throws IOException {
		PrintWriter w = new PrintWriter("D:/update_region_yahooname.sql");
		File f = new File("D:/noname3");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			sb.append("UPDATE region set yahooname='");
			sb.append(ss[5] + "'");
			sb.append(" WHERE statecode='");
			sb.append(ss[1] + "'");

			sb.append(" AND displayname='");
			sb.append(ss[2].replaceAll("'", "''") + "'");
			
			sb.append("\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	
	public static String updateCityCityCode() throws IOException {
		PrintWriter w = new PrintWriter("D:/update_city_citycode.sql");
		File f = new File("D:/city_citycode");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			sb.append("UPDATE city set citycode='");
			sb.append(ss[3] + "'");
			sb.append(" WHERE statecode='");
			sb.append(ss[2] + "'");

			sb.append(" AND name='");
			sb.append(ss[1].replaceAll("'", "''") + "'");
			
			sb.append("\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	public static String updateStateGoogleID() throws IOException {
		PrintWriter w = new PrintWriter("D:/update_state_googleid.sql");
		File f = new File("D:/state_google_id");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			sb.append("UPDATE state set googleid='");
			sb.append(ss[7] + "'");
			sb.append(" WHERE statecode='");
			sb.append(ss[0].replaceAll("'", "''") + "'");
			sb.append("\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	
//	private static final int[] DEAL_IDs = {71,173,192,195,379,382,388};
	public static String generateVoucher() throws IOException {
		
		PrintWriter w = new PrintWriter("D:/vouchers_insert2.sql");
		w.write("DECLARE @vid int\r\n");
		File f = new File("D:/Noname5");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		Pattern p = Pattern.compile("(\\d+)/(\\d+)-(\\d+)/(\\d+)");
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			
			String dealid = ss[0];
//			String dealid = String.valueOf(DEAL_IDs[new Random().nextInt(7)]);
			String status = String.valueOf(VoucherStatus.valueOf(ss[3]).id);
			String code = ss[4];
			String engine = "1";
			String date = ss[6];
			sb.append("INSERT INTO dbo.Voucher (status, dealid,vouchercode,dealengineid) values(");
			sb.append(status + "," + dealid + ",'" + code + "'," + engine);
			sb.append(")\r\n");
			
			sb.append("SELECT @vid = @@IDENTITY \r\n");
			Matcher m = p.matcher(date);
			if(m.matches()) {
				Integer sd = Integer.parseInt(m.group(2));
				Integer ed = Integer.parseInt(m.group(4));
				
				for(int i = sd; i < sd + 1; i ++) {
					String d = m.group(1) + "/" + i + "/2011";
					if(Integer.parseInt(status) > 2) {
						sb.append("INSERT INTO dbo.VoucherHistory (voucherid,status,changetime,modifiedby) values(");
						sb.append("@vid" + "," + "2" + "," + "'" + d + "'" + "," + "'Google'");
						sb.append(")\r\n");
					}
					
					sb.append("INSERT INTO dbo.VoucherHistory (voucherid,status,changetime,modifiedby) values(");
					sb.append("@vid" + "," + status + "," + "'" + d + "'" + "," + "'Google'");
					sb.append(")\r\n");
				}
			} else {
				if(Integer.parseInt(status) > 2) {
					sb.append("INSERT INTO dbo.VoucherHistory (voucherid,status,changetime,modifiedby) values(");
					sb.append("@vid" + "," + "1" + "," + "'" + date + "'" + "," + "'Google'");
					sb.append(")\r\n");
				}
				
				sb.append("INSERT INTO dbo.VoucherHistory (voucherid,status,changetime,modifiedby) values(");
				sb.append("@vid" + "," + status + "," + "'" + date + "'" + "," + "'Google'");
				sb.append(")\r\n");
			}
			sb.append("\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	
	public static String generate() throws IOException {
		PrintWriter w = new PrintWriter("D:/global_statecode.sql");
		File f = new File("D:/Noname1");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			sb.append("INSERT INTO dbo.state(statecode, name, woeid, marchexstateid, adherestateid, country, facebookstateid) values (");
			for(int i = 0; i < 7; i ++) {
				
				sb.append(ss[i].equals("NULL") || i == 2 || i== 3||i==4||i==6? ss[i] : "'" + ss[i].replaceAll("'", "''") + "'");
				if(i < 6) sb.append(",");
				
			}
			sb.append(")\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	
	public static String generateStateToSearchEngineid() throws IOException {
		PrintWriter w = new PrintWriter("D:/statetosearchengineid.sql");
		File f = new File("D:/statetosearchengineid");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		
		while((s = r.readLine()) != null) {
			StringBuffer sb = new StringBuffer();
			String[] ss = s.split("[^ \\S]");
			sb.append("INSERT INTO dbo.statetosearchengineid(statecode, searchengineid) values (");
			for(int i = 0; i < 2; i ++) {
				
				sb.append(ss[i].equals("NULL") ? ss[i] : "'" + ss[i].replaceAll("'", "''") + "'");
				if(i < 1) sb.append(",");
				
			}
			sb.append(")\r\n");
			w.write(sb.toString());
		}
		w.close();
		return "";
	}
	
	public static String geUSStateCodes() throws IOException {
		File f = new File("D:/Noname2");
		BufferedReader r = new BufferedReader(new FileReader(f));
		String s = null;
		StringBuffer sb = new StringBuffer();
		
		while((s = r.readLine()) != null) {
			if(!s.startsWith("US-")) {
				System.out.println(s);
				sb.append("'" + s.replaceAll("\\w{2}-", "") + "',");
			}
			
		}
		return sb.toString();
	}
}
